package com.bbs.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.bbs.bean.BBSOfBitAuto;
import com.car.util.ConnectionPool;
import com.car.util.Util;

public class BBSDaoOfBitAuto {
	private Connection conn = ConnectionPool.getConnection("bitauto", "root", "zhouzhou100428", "192.168.1.168");
	private Connection co = ConnectionPool.getConnection("carvoice_core", "root", "zhouzhou100428", "192.168.1.168");
	private Logger logger = Logger.getLogger(BBSDaoOfBitAuto.class.getName());

	public List<String> getUrlSpell() {
		List<String> urlSpells = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT DISTINCT bitauto_bbsurl AS urlSpell FROM core_brand_bitauto WHERE bitauto_urlspell NOT LIKE '%allroad'";
		try {
			st = co.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String urlSpell = rs.getString("urlSpell");
				if ((urlSpell + "**").length() > 2) {
					urlSpells.add(urlSpell);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return urlSpells;
	}

	public void insertBBS(BBSOfBitAuto bbsOfBitAuto, int id) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO bitauto_bbs_" + id
				+ "(bbs_bid,bbs_bt,bbs_sfwd,bbs_sfjh,bbs_nr,bbs_ftsj,bbs_hfs,bbs_djl,bbs_yhid,bbs_yhdj,bbs_tzs,bbs_tzjh,bbs_dq,bbs_zc) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bbsOfBitAuto.getBbs_bid());
			ps.setString(2, bbsOfBitAuto.getBbs_bt());
			ps.setInt(3, bbsOfBitAuto.getBbs_sfwd());
			ps.setInt(4, bbsOfBitAuto.getBbs_sfjh());
			ps.setString(5, bbsOfBitAuto.getBbs_nr());
			ps.setDate(6, (Date) Util.getDate(bbsOfBitAuto.getBbs_ftsj()));
			ps.setInt(7, bbsOfBitAuto.getBbs_hfs());
			ps.setInt(8, bbsOfBitAuto.getBbs_djl());
			ps.setInt(9, bbsOfBitAuto.getBbs_yhid());
			ps.setString(10, bbsOfBitAuto.getBbs_yhdj());
			ps.setInt(11, bbsOfBitAuto.getBbs_tzs());
			ps.setInt(12, bbsOfBitAuto.getBbs_tzjh());
			ps.setString(13, bbsOfBitAuto.getBbs_dq());
			ps.setDate(14, (Date) Util.getDate(bbsOfBitAuto.getBbs_zc()));
			ps.executeUpdate();
		} catch (SQLException e) {
		}

	}

	// bitauto_ltgxsj bitauto_kbgxsj
	public void updateBBSTime(String type, String urlSpell) {
		PreparedStatement ps = null;
		String sql = "UPDATE core_brand_bitauto AS bit SET bit." + type + "=NOW() WHERE bit.bitauto_urlspell='"
				+ urlSpell + "'";
		try {
			ps = co.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void createTable(int tableName) {
		Statement st = null;
		String sql = "DROP TABLE IF EXISTS bitauto_bbs_" + tableName;
		String sql2 = "create table bitauto_bbs_" + tableName + " LIKE bitauto_bbs";
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql2);
		} catch (SQLException e) {
		}
	}

	public int urlSpell2ID(String urlSpell) {
		Statement st = null;
		int id = 0;
		ResultSet rs = null;
		String sql = "SELECT bitauto_bbs FROM core_brand_bitauto WHERE bitauto_bbsurl='" + urlSpell + "'";
		try {
			st = co.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				id = rs.getInt("bitauto_bbs");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;

	}

	public void insertError(String url) {
		PreparedStatement ps = null;
		String sql = "Insert into error(url) values(?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
